package com.itheima.demo10JDBC案例;

import util.DataSourceUtils;

import java.nio.file.attribute.UserPrincipalLookupService;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * 品牌数据的增删改查操作
 */
public class BrandTest {
    public static void main(String[] args) throws SQLException {
//        selectAll();
//        insetAll();
//        updateAll();
        deleteAll();

    }
    public static void deleteAll() throws SQLException {
        Connection conn = DataSourceUtils.getConnection();

        String sql = "DELETE FROM tb_brand WHERE id=?;";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1,4);
        int row = pstmt.executeUpdate();
        System.out.println(row);
        pstmt.close();
        conn.close();
    }
    public static void updateAll() throws SQLException {
        Connection conn = DataSourceUtils.getConnection();

        String sql = "UPDATE tb_brand SET brand_name=? WHERE id=?;";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,"红米");
        pstmt.setInt(2,3);
        int row = pstmt.executeUpdate();
        System.out.println(row);
        pstmt.close();
        conn.close();
    }
    public static void insetAll() throws SQLException {
        Connection conn = DataSourceUtils.getConnection();

        String sql = "INSERT INTO tb_brand VALUES (null, ?, ?,?,?,?);";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,"三星");
        pstmt.setString(2,"三星科技有限公司");
        pstmt.setInt(3,80);
        pstmt.setString(4,"三星,三星，你可还行！");
        pstmt.setByte(5,(byte) 1);
        int row = pstmt.executeUpdate();
        System.out.println(row);
        pstmt.close();
        conn.close();
    }
    public static void selectAll() throws SQLException {
// 获取Connection
        Connection conn = DataSourceUtils.getConnection();
//定义SQL：select * from tb_brand;
//执行SQL
        String sql = "SELECT * FROM tb_brand;";
//获取 PreparedStatement对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        ArrayList<Brand> list = new ArrayList<>();
//处理结果：List<Brand>
        while(rs.next()){
            int id = rs.getInt("id");
            String brandName = rs.getString("brand_name");
            String companyName = rs.getString("company_name");
            int ordered = rs.getInt("ordered");
            String description = rs.getString("description");
            byte status = rs.getByte("status");
            Brand brand = new Brand(id, brandName, companyName, ordered, description, status);
            list.add(brand);
        }
        for (Brand brand : list) {
            System.out.println(brand);
        }
//释放资源
        rs.close();
        pstmt.close();
        conn.close();

    }
//设置参数：不需要

}